using System;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.PurchasingManagement.PurchaseInvoicefolder;

namespace RBAC.Read.Api.Application.Handler.ERP.PurchasingManagement.PurchaseInvoicefolder
{
    /// <summary>
    /// 导出采购发票（含物料）为 Excel（.xls）
    /// </summary>
    public class GetExportPurchaseInvoiceCommandHandler : IRequestHandler<GetExportPurchaseInvoiceCommands, byte[]>
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<GetExportPurchaseInvoiceCommandHandler> _logger;

        public GetExportPurchaseInvoiceCommandHandler(
            IDbConnectionFactory connectionFactory,
            ILogger<GetExportPurchaseInvoiceCommandHandler> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<byte[]> Handle(GetExportPurchaseInvoiceCommands request, CancellationToken cancellationToken)
        {
            using var conn = _connectionFactory.CreateConnectionWithRetry();

            var sql = @"SELECT 
                            pi.Id,
                            pi.DocumentCode,
                            pi.BusinessType,
                            pi.SupplierId,
                            pi.InvoiceDate,
                            pi.InvoiceUpload,
                            pi.DocumentDate,
                            pi.Operator,
                            pi.TaxNumber,
                            pi.RedBlue,
                            pi.XMLFormat,
                            pi.DocumentStatus,
                            pi.Auditor,
                            pi.InvoiceType,
                            pi.InvoiceAmount,
                            pi.OFDFormat,
                            pi.BusinessStatus,
                            pi.ReviewDate,
                            pi.InvoiceCode,
                            pi.TaxAmount,
                            pi.InvoiceStatus,
                            pi.InvoiceNumber,
                            pi.TotalTax,
                            pi.Remarks,
                            pm.PurchaseInvoiceMaterialsId,
                            pm.PurchaseOrderNumber,
                            pm.Warehouse,
                            pm.InboundOrderNumber,
                            pm.MaterialCode,
                            pm.MaterialName,
                            pm.ModelSpecifications,
                            pm.MainMeasurement,
                            pm.`Number`,
                            pm.UnitPrice,
                            pm.Amount,
                            pm.TaxRate,
                            pm.TaxAmount,
                            pm.PriceTaxTotal,
                            pm.IsDel
                        FROM PurchaseInvoice pi
                        LEFT JOIN ProcurementMaterials pm 
                            ON pm.PurchaseInvoiceId = pi.Id
                        WHERE pi.IsDeleted = 0";

            var param = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(request.DocumentCode))
            {
                sql += " AND pi.DocumentCode LIKE @DocumentCode";
                param.Add("@DocumentCode", $"%{request.DocumentCode.Trim()}%");
            }
            if (request.SupplierId.HasValue)
            {
                sql += " AND pi.SupplierId = @SupplierId";
                param.Add("@SupplierId", request.SupplierId.Value);
            }
            if (request.InvoiceDateStart.HasValue)
            {
                sql += " AND pi.InvoiceDate >= @InvoiceDateStart";
                param.Add("@InvoiceDateStart", request.InvoiceDateStart.Value);
            }
            if (request.InvoiceDateEnd.HasValue)
            {
                sql += " AND pi.InvoiceDate <= @InvoiceDateEnd";
                param.Add("@InvoiceDateEnd", request.InvoiceDateEnd.Value);
            }

            sql += " ORDER BY pi.Id DESC, pm.PurchaseInvoiceMaterialsId ASC";

            var rows = await conn.QueryAsync(sql, param);

            // 构建 Excel（.xls 格式）
            IWorkbook workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("PurchaseInvoices");

            int r = 0;
            // Header
            var header = sheet.CreateRow(r++);
            string[] headers = new[]
            {
                // 发票字段
                "发票ID","单据编码","业务类型","供应商Id","开票日期","发票上传","单据日期","操作员","税号","红蓝字","XML格式","单据状态","审核员","发票类型","发票金额","OFD格式","业务状态","审核日期","发票代码","税额","发票状态","发票号码","价税合计","备注",
                // 物料字段
                "物料ID","采购订单号","仓库","入库单号","物料编码","物料名称","型号规格","主计量","数量","单价","金额","税率","税额","价税合计","是否删除"
            };
            for (int i = 0; i < headers.Length; i++) header.CreateCell(i).SetCellValue(headers[i]);

            foreach (var row in rows)
            {
                var dataRow = sheet.CreateRow(r++);
                int c = 0;
                // 发票
                dataRow.CreateCell(c++).SetCellValue((row.Id is int id) ? id : Convert.ToInt32(row.Id));
                dataRow.CreateCell(c++).SetCellValue((string?)row.DocumentCode ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.BusinessType ?? "");
                dataRow.CreateCell(c++).SetCellValue((row.SupplierId is long sid) ? sid : Convert.ToInt64(row.SupplierId));
                dataRow.CreateCell(c++).SetCellValue((row.InvoiceDate is DateTime dt) ? dt.ToString("yyyy-MM-dd") : "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.InvoiceUpload ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.DocumentDate ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Operator ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.TaxNumber ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.RedBlue ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.XMLFormat ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.DocumentStatus ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Auditor ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.InvoiceType ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.InvoiceAmount ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.OFDFormat ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.BusinessStatus ?? "");
                dataRow.CreateCell(c++).SetCellValue((row.ReviewDate is DateTime rv) ? rv.ToString("yyyy-MM-dd") : "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.InvoiceCode ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.TaxAmount ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.InvoiceStatus ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.InvoiceNumber ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.TotalTax ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Remarks ?? "");

                // 物料
                dataRow.CreateCell(c++).SetCellValue(row.PurchaseInvoiceMaterialsId == null ? "" : row.PurchaseInvoiceMaterialsId.ToString());
                dataRow.CreateCell(c++).SetCellValue((string?)row.PurchaseOrderNumber ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Warehouse ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.InboundOrderNumber ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.MaterialCode ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.MaterialName ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.ModelSpecifications ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.MainMeasurement ?? "");
                dataRow.CreateCell(c++).SetCellValue(row.Number == null ? "" : row.Number.ToString());
                dataRow.CreateCell(c++).SetCellValue((string?)row.UnitPrice ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Amount ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.TaxRate ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.TaxAmount ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.PriceTaxTotal ?? "");
                dataRow.CreateCell(c++).SetCellValue((row.IsDel is bool b) ? (b ? 1 : 0) : 0);
            }

            for (int i = 0; i < headers.Length; i++) sheet.AutoSizeColumn(i);

            using var ms = new MemoryStream();
            workbook.Write(ms);
            return ms.ToArray();
        }
    }
}
